﻿using ClosedXML.Excel;
using DocumentFormat.OpenXml.Office.CustomUI;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;

namespace MyDLL
{
    /// <summary>
    /// DataTable相关的方法
    /// </summary>
    public class DataTable_Common
    {
        /// <summary>
        /// 将一个二维数组转化为DataTable。
        /// </summary>
        /// <typeparam name="T">二维数组的类型</typeparam>
        /// <param name="inputArr">二维数组</param>
        /// <param name="headerStrArr">DataTable的表头</param>
        /// <returns>输出的DataTable</returns>
        public DataTable Arr2D_to_DataTable<T>(T[,] inputArr, string[] headerStrArr)
        {
            DataTable dataTable = new DataTable();
            DataColumnCollection dataColumnCollection = dataTable.Columns;
            //定义Headers
            for (int i = 0; i < headerStrArr.Length; i++)
            {
                dataColumnCollection.Add(headerStrArr[i], typeof(T));
            }
            //逐行添加数据
            DataRow dataRow = null;
            int colNum = inputArr.GetLength(1);
            int rowNum = inputArr.GetLength(0);
            for (int i = 0; i < rowNum; i++)//按行数循环
            {
                dataRow = dataTable.NewRow();
                for (int j = 0; j < colNum; j++)//按列循环
                {
                    dataRow[j] = inputArr[i, j];
                }
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }
        /// <summary>
        /// 将一个二维数组附加进一个DataTable。
        /// </summary>
        /// <typeparam name="T">二维数组数据类型</typeparam>
        /// <param name="dataTable">已存在的DataTable</param>
        /// <param name="arr">二维数组</param>
        public void input2DArr_to_DataTable<T>(DataTable dataTable, T[,] arr)
        {
            int rowNum = arr.GetLength(0);
            int colNum = arr.GetLength(1);
            for (int i = 0; i < rowNum; i++)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int j = 0; j < colNum; j++)
                {
                    dataRow[j] = arr[i,j];
                }
                dataTable.Rows.Add(dataRow);
            }
        }
        /// <summary>
        /// 创建一个带自定义Headers的DataTable
        /// </summary>
        /// <typeparam name="T">DataTable各列数据类型</typeparam>
        /// <param name="headerStrArr">Headers数组</param>
        /// <returns>返回创建的DataTable</returns>
        public DataTable DataTable_HeadersSetting<T>(string[] headerStrArr)
        {
            DataTable dataTable = new DataTable();
            DataColumnCollection dataColumnCollection = dataTable.Columns;
            //定义Headers
            for (int i = 0; i < headerStrArr.Length; i++)
            {
                dataColumnCollection.Add(headerStrArr[i], typeof(T));
            }
            return dataTable;
        }

        /// <summary>
        /// 将一维数组添加到DataTable的行中，注意数组的元素个数要与DataTable的列数一致。
        /// </summary>
        /// <typeparam name="T">一维数组的数据类型</typeparam>
        /// <param name="dataTable">目标DataTable</param>
        /// <param name="arr">一维数组</param>
        public void input1DArr_to_DataTable<T>(DataTable dataTable,T[] arr)
        {
            DataRow dataRow = dataTable.NewRow();
            for (int i = 0; i < arr.Length; i++)
            {
                dataRow[i] = arr[i];
            }
            dataTable.Rows.Add(dataRow);
        }
        /// <summary>
        /// 将DataTable导入CSV文件，数据量较小时适用。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV">CSV文件全名</param>
        public void DataTable_to_CSV(DataTable dataTable,string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Create, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            //导出Headers
            int dt_colNum = dataTable.Columns.Count;
            int dt_rowNum = dataTable.Rows.Count;
            for (int i = 0; i < dt_colNum; i++)
            {
                data += dataTable.Columns[i].ColumnName.ToString();
                if (i < dt_colNum - 1)
                {
                    data += ",";
                }
            }
            streamWriter.WriteLine(data);
            //导出数据
            for (int i = 0; i < dt_rowNum; i++)
            {
                data = "";
                for (int j = 0; j < dt_colNum; j++)
                {

                    data += dataTable.Rows[i][j].ToString();
                    if (j < dt_colNum - 1)
                    {
                        data += ",";
                    }
                }
                streamWriter.WriteLine(data);
            }
            streamWriter.Close();
            fileStream.Close();
        }

        /// <summary>
        /// 将DataTable导入CSV文件
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV">CSV文件的完整路径</param>
        public static void DataTable_to_CSV_StackOverFlow_2020_9_2(DataTable dataTable, string fullFileName_CSV)
        {
            var lines = new List<string>();

            string[] columnNames = dataTable.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName).
                                              ToArray();

            var header = string.Join(",", columnNames);
            lines.Add(header);

            var valueLines = dataTable.AsEnumerable()
                               .Select(row => string.Join(",", row.ItemArray));
            lines.AddRange(valueLines);

            File.WriteAllLines(fullFileName_CSV, lines);
        }

        /// <summary>
        /// 仅将DataTable表头导入CSV文件（无需事先创建CSV文件）。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV"></param>
        public void DataTable_to_CSV_HeadersOnly(DataTable dataTable, string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Create, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            //导出Headers
            int dt_colNum = dataTable.Columns.Count;
            for (int i = 0; i < dt_colNum; i++)
            {
                data += dataTable.Columns[i].ColumnName.ToString();
                if (i < dt_colNum - 1)
                {
                    data += ",";
                }
            }
            streamWriter.WriteLine(data);
            streamWriter.Close();
            fileStream.Close();
        }

        /// <summary>
        /// 将DataTable数据部分导入CSV文件，不包含表头（注意需要先创建好CSV文件）。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV"></param>
        public void DataTable_to_CSV_Append(DataTable dataTable, string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Append, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            int dt_colNum = dataTable.Columns.Count;
            int dt_rowNum = dataTable.Rows.Count;
            //导出数据
            for (int i = 0; i < dt_rowNum; i++)
            {
                data = "";
                for (int j = 0; j < dt_colNum; j++)
                {

                    data += dataTable.Rows[i][j].ToString();
                    if (j < dt_colNum - 1)
                    {
                        data += ",";
                    }
                }
                streamWriter.WriteLine(data);
            }
            streamWriter.Close();
            fileStream.Close();
        }

        /// <summary>
        /// Datatable生成xlsx表格并返回路径
        /// </summary>
        /// <param name="m_DataTable">Datatable</param>
        /// <param name="s_FileName">完整的文件名，记得要有后缀".xlsx"</param>
        /// <returns></returns>
        public  static string DataTableToExcel(DataTable m_DataTable, string s_FileName)
        {
            string FileName = s_FileName; //文件存放路径
            if (File.Exists(FileName)) //存在则删除
            {
                File.Delete(FileName);
            }
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, Encoding.Unicode);
            for (int i = 0; i < m_DataTable.Columns.Count; i++)
            {
                strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < m_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < m_DataTable.Columns.Count; j++)
                {
                    if (m_DataTable.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9); //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            return FileName; //返回生成文件的绝对路径
        }

        /// <summary>
        /// 将DataTable导入xlsx文件，注意需要在主程序引用DocumentFormat.OpenXml和ClosedXml，同时需要注意版本问题。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="fullFilePath">完整文件路径</param>
        /// <param name="shtName">工作表名称</param>
        public static void DataTable_to_Excel_UsingClosedXML(DataTable dt, string fullFilePath,string shtName="Sheet1")
        {
            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(dt,shtName);
            wb.SaveAs(fullFilePath);
        }

        /// <summary>
        /// 将DataTable导出到Xlsx。
        /// </summary>
        /// <param name="m_DataTable">待导出的DataTable</param>
        /// <param name="s_FileName">Xlsx文件的完整路径</param>
        public static void DataTableToExcel_no_return(DataTable m_DataTable, string s_FileName)
        {
            string FileName = s_FileName; //文件存放路径
            if (File.Exists(FileName)) //存在则删除
            {
                File.Delete(FileName);
            }
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, Encoding.Unicode);
            for (int i = 0; i < m_DataTable.Columns.Count; i++)
            {
                strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < m_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < m_DataTable.Columns.Count; j++)
                {
                    if (m_DataTable.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9); //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
        }
    }
    public class DataTable_Common_Static
    {
        /// <summary>
        /// 将一个二维数组转化为DataTable。
        /// </summary>
        /// <typeparam name="T">二维数组的类型</typeparam>
        /// <param name="inputArr">二维数组</param>
        /// <param name="headerStrArr">DataTable的表头</param>
        /// <returns>输出的DataTable</returns>
        public static DataTable Arr2D_to_DataTable<T>(T[,] inputArr, string[] headerStrArr)
        {
            DataTable dataTable = new DataTable();
            DataColumnCollection dataColumnCollection = dataTable.Columns;
            //定义Headers
            for (int i = 0; i < headerStrArr.Length; i++)
            {
                dataColumnCollection.Add(headerStrArr[i], typeof(T));
            }
            //逐行添加数据
            DataRow dataRow = null;
            int colNum = inputArr.GetLength(1);
            int rowNum = inputArr.GetLength(0);
            for (int i = 0; i < rowNum; i++)//按行数循环
            {
                dataRow = dataTable.NewRow();
                for (int j = 0; j < colNum; j++)//按列循环
                {
                    dataRow[j] = inputArr[i, j];
                }
                dataTable.Rows.Add(dataRow);
            }
            return dataTable;
        }

        /// <summary>
        /// 获取DataTable的Headers，并返回字符串数组。
        /// </summary>
        /// <param name="dt">DataTable对象</param>
        /// <returns>返回字符串数组</returns>
        public static string[] GetHeaders(DataTable dt)
        {
            return dt.Columns.Cast<DataColumn>()
                                 .Select(x => x.ColumnName)
                                 .ToArray();
        }

        /// <summary>
        /// 将DataTable转为二维数组（包括headers）。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>返回二维数组</returns>
        public static string[,] DataTable_To_Arr2D_WithHeaders(DataTable dt)
        {
            string[,] resArr = new string[1 + dt.Rows.Count, dt.Columns.Count];
            
            //获取headers
            string[] headers = GetHeaders(dt);
            for (int i = 0; i < headers.Length; i++)
            {
                resArr[0, i] = headers[i];
            }

            //获取dt内容
            for (int i = 1; i < resArr.GetLength(0); i++)
            {
                for (int j = 0; j < resArr.GetLength(1); j++)
                {
                    resArr[i, j] = dt.Rows[i - 1][j].ToString();
                }
            }
            return resArr;
        }

        /// <summary>
        /// 将DataTable转为二维数组（不包括headers）。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <returns>返回二维数组</returns>
        public static string[,] DataTable_To_Arr2D_WithOutHeaders(DataTable dt)
        {
            string[,] resArr = new string[dt.Rows.Count, dt.Columns.Count];

            //获取dt内容
            for (int i = 0; i < resArr.GetLength(0); i++)
            {
                for (int j = 0; j < resArr.GetLength(1); j++)
                {
                    resArr[i, j] = dt.Rows[i][j].ToString();
                }
            }
            return resArr;
        }

        /// <summary>
        /// datatable转list
        /// https://blog.csdn.net/bytxl/article/details/44033823
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="table"></param>
        /// <returns></returns>
        public IList<T> GetList<T>(DataTable table)
        {
            IList<T> list = new List<T>(); //里氏替换原则
            T t = default(T);
            PropertyInfo[] propertypes = null;
            string tempName = string.Empty;
            foreach (DataRow row in table.Rows)
            {
                t = Activator.CreateInstance<T>(); //创建指定类型的实例

               propertypes = t.GetType().GetProperties(); //得到类的属性
                foreach (PropertyInfo pro in propertypes)
                {
                    tempName = pro.Name;
                    if (table.Columns.Contains(tempName.ToUpper()))
                    {
                        object value = row[tempName];
                        if (value is System.DBNull)
                        {
                            value = "";
                        }
                        pro.SetValue(t, value, null);
                    }
                }
                list.Add(t);
            }
            return list;
        }

        /// <summary>
        /// 将一个二维数组附加进一个DataTable。
        /// </summary>
        /// <typeparam name="T">二维数组数据类型</typeparam>
        /// <param name="dataTable">已存在的DataTable</param>
        /// <param name="arr">二维数组</param>
        public static void input2DArr_to_DataTable<T>(DataTable dataTable, T[,] arr)
        {
            int rowNum = arr.GetLength(0);
            int colNum = arr.GetLength(1);
            for (int i = 0; i < rowNum; i++)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int j = 0; j < colNum; j++)
                {
                    dataRow[j] = arr[i, j];
                }
                dataTable.Rows.Add(dataRow);
            }
        }
        /// <summary>
        /// 创建一个带自定义Headers的DataTable
        /// </summary>
        /// <typeparam name="T">DataTable各列数据类型</typeparam>
        /// <param name="headerStrArr">Headers数组</param>
        /// <returns>返回创建的DataTable</returns>
        public static DataTable DataTable_HeadersSetting<T>(string[] headerStrArr)
        {
            DataTable dataTable = new DataTable();
            DataColumnCollection dataColumnCollection = dataTable.Columns;
            //定义Headers
            for (int i = 0; i < headerStrArr.Length; i++)
            {
                dataColumnCollection.Add(headerStrArr[i], typeof(T));
            }
            return dataTable;
        }
        /// <summary>
        /// 将一维数组添加到DataTable的行中，注意数组的元素个数要与DataTable的列数一致。
        /// </summary>
        /// <typeparam name="T">一维数组的数据类型</typeparam>
        /// <param name="dataTable">目标DataTable</param>
        /// <param name="arr">一维数组</param>
        public static void input1DArr_to_DataTable<T>(DataTable dataTable, T[] arr)
        {
            DataRow dataRow = dataTable.NewRow();
            for (int i = 0; i < arr.Length; i++)
            {
                dataRow[i] = arr[i];
            }
            dataTable.Rows.Add(dataRow);
        }

        /// <summary>
        /// 将一维数组添加到DataTable的行中，指定输入的列号
        /// </summary>
        /// <typeparam name="T">一维数组的数据类型</typeparam>
        /// <param name="dataTable">目标DataTable</param>
        /// <param name="arr">一维数组</param>
        /// <param name="colIndexes">带输入的列号数组，注意元素数量要与arr相同；默认为null，此时意味着输入到DataTable整行</param>
        public static void input1DArr_to_DataTable_ByRow_Specify_Columns<T>(DataTable dataTable, T[] arr, int[] colIndexes = null)
        {
            if (colIndexes == null)
                colIndexes = BasicDataProcess_Common.IntArrayGenerator(0, 1, arr.Length);

            DataRow dataRow = dataTable.NewRow();
            for (int i = 0; i < arr.Length; i++)
            {
                dataRow[colIndexes[i]] = arr[i];
            }
            dataTable.Rows.Add(dataRow);
        }

        /// <summary>
        /// 将一维数组添加到DataTable的指定列中（如果数组元素比DataTable行数多，可自行决定是否增加DataTable行数）。
        /// </summary>
        /// <typeparam name="T">一维数组的数据类型</typeparam>
        /// <param name="dataTable">目标DataTable</param>
        /// <param name="arr">一维数组</param>
        /// <param name="colIndex">指定插入列号</param>
        /// <param name="isAddRows">是否增加行数，默认不增加</param>
        public static void input1DArr_to_DataTable_ByCol<T>(DataTable dataTable, T[] arr, int colIndex,bool isAddRows = false)
        {
            if (isAddRows)
            {
                AddRows(dataTable, arr.Length - dataTable.Rows.Count);
            }
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                dataTable.Rows[i][colIndex] = arr[i];
            }
        }

        /// <summary>
        /// 将一维数组添加到DataTable的指定列中（如果数组元素比DataTable行数多，可自行决定是否增加DataTable行数）。
        /// </summary>
        /// <typeparam name="T">一维数组的数据类型</typeparam>
        /// <param name="dataTable">目标DataTable</param>
        /// <param name="arr">一维数组</param>
        /// <param name="colHeaderText">指定插入列标文字</param>
        /// <param name="isAddRows">是否增加行数，默认不增加</param>
        public static void input1DArr_to_DataTable_ByCol<T>(DataTable dataTable, T[] arr, string colHeaderText, bool isAddRows = false)
        {
            if (isAddRows)
            {
                AddRows(dataTable, arr.Length - dataTable.Rows.Count);
            }
            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                dataTable.Rows[i][colHeaderText] = arr[i];
            }
        }


        /// <summary>
        /// 将DataTable导入CSV文件，数据量较小时适用。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV">CSV文件全名</param>
        public static void DataTable_to_CSV(DataTable dataTable, string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Create, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            //导出Headers
            int dt_colNum = dataTable.Columns.Count;
            int dt_rowNum = dataTable.Rows.Count;
            for (int i = 0; i < dt_colNum; i++)
            {
                data += dataTable.Columns[i].ColumnName.ToString();
                if (i < dt_colNum - 1)
                {
                    data += ",";
                }
            }
            streamWriter.WriteLine(data);
            //导出数据
            for (int i = 0; i < dt_rowNum; i++)
            {
                data = "";
                for (int j = 0; j < dt_colNum; j++)
                {

                    data += dataTable.Rows[i][j].ToString();
                    if (j < dt_colNum - 1)
                    {
                        data += ",";
                    }
                }
                streamWriter.WriteLine(data);
            }
            streamWriter.Close();
            fileStream.Close();
        }

        /// <summary>
        /// 将DataTable导入CSV文件
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV">CSV文件的完整路径</param>
        public static void DataTable_to_CSV_StackOverFlow_2020_9_2(DataTable dataTable, string fullFileName_CSV)
        {
            var lines = new List<string>();

            string[] columnNames = dataTable.Columns.Cast<DataColumn>().
                                              Select(column => column.ColumnName).
                                              ToArray();

            var header = string.Join(",", columnNames);
            lines.Add(header);

            var valueLines = dataTable.AsEnumerable()
                               .Select(row => string.Join(",", row.ItemArray));
            lines.AddRange(valueLines);

            File.WriteAllLines(fullFileName_CSV, lines);
        }

        /// <summary>
        /// 仅将DataTable表头导入CSV文件（无需事先创建CSV文件）。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV"></param>
        public static void DataTable_to_CSV_HeadersOnly(DataTable dataTable, string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Create, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            //导出Headers
            int dt_colNum = dataTable.Columns.Count;
            for (int i = 0; i < dt_colNum; i++)
            {
                data += dataTable.Columns[i].ColumnName.ToString();
                if (i < dt_colNum - 1)
                {
                    data += ",";
                }
            }
            streamWriter.WriteLine(data);
            streamWriter.Close();
            fileStream.Close();
        }
        /// <summary>
        /// 将DataTable数据部分导入CSV文件，不包含表头（注意需要先创建好CSV文件）。
        /// </summary>
        /// <param name="dataTable"></param>
        /// <param name="fullFileName_CSV"></param>
        public static void DataTable_to_CSV_Append(DataTable dataTable, string fullFileName_CSV)
        {
            FileStream fileStream = new FileStream(fullFileName_CSV, FileMode.Append, FileAccess.Write);
            StreamWriter streamWriter = new StreamWriter(fileStream, Encoding.UTF8);
            string data = "";
            int dt_colNum = dataTable.Columns.Count;
            int dt_rowNum = dataTable.Rows.Count;
            //导出数据
            for (int i = 0; i < dt_rowNum; i++)
            {
                data = "";
                for (int j = 0; j < dt_colNum; j++)
                {

                    data += dataTable.Rows[i][j].ToString();
                    if (j < dt_colNum - 1)
                    {
                        data += ",";
                    }
                }
                streamWriter.WriteLine(data);
            }
            streamWriter.Close();
            fileStream.Close();
        }

        /// <summary>
        /// Datatable生成xlsx表格并返回路径
        /// </summary>
        /// <param name="m_DataTable">Datatable</param>
        /// <param name="s_FileName">完整的文件名，记得要有后缀".xlsx"</param>
        /// <returns></returns>
        public static string DataTableToExcel(DataTable m_DataTable, string s_FileName)
        {
            string FileName = s_FileName; //文件存放路径
            if (File.Exists(FileName)) //存在则删除
            {
                File.Delete(FileName);
            }
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, Encoding.Unicode);
            for (int i = 0; i < m_DataTable.Columns.Count; i++)
            {
                strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < m_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < m_DataTable.Columns.Count; j++)
                {
                    if (m_DataTable.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9); //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
            return FileName; //返回生成文件的绝对路径
        }

        /// <summary>
        /// 将DataTable导入xlsx文件，注意需要在主程序引用DocumentFormat.OpenXml和ClosedXml，同时需要注意版本问题。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="fullFilePath">完整文件路径</param>
        /// <param name="shtName">工作表名称</param>
        public static void DataTable_to_Excel_UsingClosedXML(DataTable dt, string fullFilePath, string shtName = "Sheet1")
        {
            XLWorkbook wb = new XLWorkbook();
            wb.Worksheets.Add(dt, shtName);
            wb.SaveAs(fullFilePath);
        }

        /// <summary>
        /// 将DataTable导出到Xlsx。
        /// </summary>
        /// <param name="m_DataTable">待导出的DataTable</param>
        /// <param name="s_FileName">Xlsx文件的完整路径</param>
        public static void DataTableToExcel_no_return(DataTable m_DataTable, string s_FileName)
        {
            string FileName = s_FileName; //文件存放路径
            if (File.Exists(FileName)) //存在则删除
            {
                File.Delete(FileName);
            }
            FileStream objFileStream;
            StreamWriter objStreamWriter;
            string strLine = "";
            objFileStream = new FileStream(FileName, FileMode.OpenOrCreate, FileAccess.Write);
            objStreamWriter = new StreamWriter(objFileStream, Encoding.Unicode);
            for (int i = 0; i < m_DataTable.Columns.Count; i++)
            {
                strLine = strLine + m_DataTable.Columns[i].Caption.ToString() + Convert.ToChar(9); //写列标题
            }
            objStreamWriter.WriteLine(strLine);
            strLine = "";
            for (int i = 0; i < m_DataTable.Rows.Count; i++)
            {
                for (int j = 0; j < m_DataTable.Columns.Count; j++)
                {
                    if (m_DataTable.Rows[i].ItemArray[j] == null)
                        strLine = strLine + " " + Convert.ToChar(9); //写内容
                    else
                    {
                        string rowstr = "";
                        rowstr = m_DataTable.Rows[i].ItemArray[j].ToString();
                        if (rowstr.IndexOf("\r\n") > 0)
                            rowstr = rowstr.Replace("\r\n", " ");
                        if (rowstr.IndexOf("\t") > 0)
                            rowstr = rowstr.Replace("\t", " ");
                        strLine = strLine + rowstr + Convert.ToChar(9);
                    }
                }
                objStreamWriter.WriteLine(strLine);
                strLine = "";
            }
            objStreamWriter.Close();
            objFileStream.Close();
        }

        /// <summary>
        /// 在dt中添加多行。
        /// </summary>
        /// <param name="dt">DataTable对象</param>
        /// <param name="rowNum">行数</param>
        public static void AddRows(DataTable dt, int rowNum)
        {
            for (int i = 0; i < rowNum; i++)
            {
                dt.Rows.Add(dt.NewRow());
            }
        }

        /// <summary>
        /// 将DataTable以二维数组的形式返回。
        /// </summary>
        /// <param name="dt">DataTable</param>
        /// <param name="lowerBound">lowerBound，默认为0。</param>
        /// <param name="getHearders">是否获取标题行，默认为是。</param>
        /// <returns>返回二维数组</returns>
        public static object[,] DataTableToArray2D(DataTable dt,int lowerBound = 0,bool getHearders=true)
        {
            object[,] res = null;
            if (getHearders)    //如果获取Headers
            {
                res = (object[,])Array.CreateInstance(typeof(object), new int[2] { dt.Rows.Count + 1, dt.Columns.Count }, new int[2] { lowerBound, lowerBound });
                object[] resHeaders= dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();
                for (int j = 0; j < resHeaders.Length; j++)
                {
                    res[lowerBound, j + lowerBound] = resHeaders[j];
                }
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        res[i + lowerBound + 1, j + lowerBound] = (dt.Rows[i][j] == DBNull.Value) ? "" : dt.Rows[i][j]; //如果dt.Rows[i][j]值为空，则res对应元素值为字符串空，否则取dt.Rows[i][j]值。
                    }
                }
            }
            else                //如果不获取Headers
            {
                res = (object[,])Array.CreateInstance(typeof(object), new int[2] { dt.Rows.Count, dt.Columns.Count }, new int[2] { lowerBound, lowerBound });
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        res[i + lowerBound, j + lowerBound] = (dt.Rows[i][j] == DBNull.Value) ? "" : dt.Rows[i][j]; //如果dt.Rows[i][j]值为空，则res对应元素值为字符串空，否则取dt.Rows[i][j]值。
                    }
                }
            }
            
            return res;
        }

        /// <summary>
        /// 将dt中的指定列以数组形式返回，不包含列头。
        /// </summary>
        /// <typeparam name="T">指定数据类型，通常为dt指定列的数据类型，可用dt.Columns[i].DataType来获取</typeparam>
        /// <param name="dt">DataTable</param>
        /// <param name="i">指定列号</param>
        /// <returns>返回数组</returns>
        public static object[] DataTableColumnToArray1D(DataTable dt, int i)
        {
            int rowNum = dt.Rows.Count;
            object[] res = new object[rowNum];
            for (int j = 0; j < rowNum; j++)
            {
                res[j] = dt.Rows[j][i];
            }
            return res;
        }

        /// <summary>
        /// 将dt中的指定列以数组形式返回，不包含列头，指定长度。
        /// </summary>
        /// <typeparam name="T">指定数据类型，通常为dt指定列的数据类型，可用dt.Columns[i].DataType来获取</typeparam>
        /// <param name="dt">DataTable</param>
        /// <param name="i">指定列号</param>
        /// <param name="rowNum">指定长度</param>
        /// <returns>返回数组</returns>
        public static object[] DataTableColumnToArray1D(DataTable dt, int i,int rowNum)
        {
            object[] res = new object[rowNum];
            for (int j = 0; j < rowNum; j++)
            {
                res[j] = dt.Rows[j][i];
            }
            return res;
        }

        /// <summary>
        /// 将dt中的指定列以数组形式返回，不包含列头。
        /// </summary>
        /// <typeparam name="T">指定数据类型，通常为dt指定列的数据类型，可用dt.Columns[i].DataType来获取</typeparam>
        /// <param name="dt">DataTable</param>
        /// <param name="header">指定列名</param>
        /// <returns>返回数组</returns>
        public static object[] DataTableColumnToArray1D(DataTable dt, string header)
        {
            int rowNum = dt.Rows.Count;
            object[] res = new object[rowNum];
            for (int j = 0; j < rowNum; j++)
            {
                res[j] = dt.Rows[j][header];
            }
            return res;
        }

        /// <summary>
        /// 将dt中的指定列以数组形式返回，不包含列头，指定长度。
        /// </summary>
        /// <typeparam name="T">指定数据类型，通常为dt指定列的数据类型，可用dt.Columns[i].DataType来获取</typeparam>
        /// <param name="dt">DataTable</param>
        /// <param name="header">指定列名</param>
        /// <param name="rowNum">指定长度</param>
        /// <returns>返回数组</returns>
        public static object[] DataTableColumnToArray1D(DataTable dt, string header, int rowNum)
        {
            object[] res = new object[rowNum];
            for (int j = 0; j < rowNum; j++)
            {
                res[j] = dt.Rows[j][header];
            }
            return res;
        }
    }
}
